{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "450ccb90",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "86bbbe96",
   "metadata": {},
   "outputs": [],
   "source": [
    "fout = open(\"自动生成-sql语句.sql\", \"w\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1776d7d8",
   "metadata": {},
   "source": [
    "### 用户表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a86d256f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>gender</th>\n",
       "      <th>是否不评分</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>宋江</td>\n",
       "      <td>男</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>扈三娘</td>\n",
       "      <td>女</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>孙二娘</td>\n",
       "      <td>女</td>\n",
       "      <td>是</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>武松</td>\n",
       "      <td>男</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>鲁智深</td>\n",
       "      <td>男</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>李逵</td>\n",
       "      <td>男</td>\n",
       "      <td>是</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>顾大嫂</td>\n",
       "      <td>女</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id name gender 是否不评分\n",
       "0   1   宋江      男     否\n",
       "1   2  扈三娘      女     否\n",
       "2   3  孙二娘      女     是\n",
       "3   4   武松      男     否\n",
       "4   5  鲁智深      男     否\n",
       "5   6   李逵      男     是\n",
       "6   7  顾大嫂      女     否"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_user = pd.read_excel(\"人工构造-user表.xlsx\")\n",
    "df_user"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "da21dfda",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_before = \"\"\"\n",
    "insert into user\n",
    "(name, gender) \n",
    "values \n",
    "\"\"\"\n",
    "\n",
    "sqls = []\n",
    "for idx, row in df_user.iterrows():\n",
    "    name, gender = row[\"name\"], row[\"gender\"]\n",
    "    sqls.append(f\"\"\"('{name}', '{gender}')\"\"\")\n",
    "\n",
    "sql = sql_before + \", \\n\".join(sqls) + \";\"\n",
    "\n",
    "fout.write(sql + \"\\n\\n\")\n",
    "fout.flush()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68886dbc",
   "metadata": {},
   "source": [
    "### 电影表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "54f0648a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>title</th>\n",
       "      <th>pubyear</th>\n",
       "      <th>category</th>\n",
       "      <th>豆瓣评分</th>\n",
       "      <th>是否不评分</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>功夫熊猫</td>\n",
       "      <td>2008</td>\n",
       "      <td>动画片</td>\n",
       "      <td>8.2</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>唐伯虎点秋香</td>\n",
       "      <td>1993</td>\n",
       "      <td>喜剧片</td>\n",
       "      <td>8.7</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>泰坦尼克号</td>\n",
       "      <td>1997</td>\n",
       "      <td>爱情片</td>\n",
       "      <td>9.4</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>武状元苏乞儿</td>\n",
       "      <td>1992</td>\n",
       "      <td>喜剧片</td>\n",
       "      <td>8.2</td>\n",
       "      <td>是</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>盗梦空间</td>\n",
       "      <td>2010</td>\n",
       "      <td>科幻片</td>\n",
       "      <td>9.4</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>超体</td>\n",
       "      <td>2014</td>\n",
       "      <td>科幻片</td>\n",
       "      <td>7.4</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>阿凡达</td>\n",
       "      <td>2009</td>\n",
       "      <td>科幻片</td>\n",
       "      <td>8.8</td>\n",
       "      <td>是</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>疯狂动物城</td>\n",
       "      <td>2016</td>\n",
       "      <td>动画片</td>\n",
       "      <td>9.2</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>魂断蓝桥</td>\n",
       "      <td>1940</td>\n",
       "      <td>爱情片</td>\n",
       "      <td>8.8</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>千与千寻</td>\n",
       "      <td>2001</td>\n",
       "      <td>动画片</td>\n",
       "      <td>9.4</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>西虹市首富</td>\n",
       "      <td>2018</td>\n",
       "      <td>喜剧片</td>\n",
       "      <td>6.6</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>黑客帝国</td>\n",
       "      <td>1999</td>\n",
       "      <td>科幻片</td>\n",
       "      <td>9.1</td>\n",
       "      <td>是</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>美女与野兽</td>\n",
       "      <td>1991</td>\n",
       "      <td>爱情片</td>\n",
       "      <td>8.5</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>神偷奶爸</td>\n",
       "      <td>2010</td>\n",
       "      <td>动画片</td>\n",
       "      <td>8.6</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15</td>\n",
       "      <td>逃学威龙</td>\n",
       "      <td>1991</td>\n",
       "      <td>喜剧片</td>\n",
       "      <td>8.1</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id   title  pubyear category  豆瓣评分 是否不评分\n",
       "0    1    功夫熊猫     2008      动画片   8.2     否\n",
       "1    2  唐伯虎点秋香     1993      喜剧片   8.7     否\n",
       "2    3   泰坦尼克号     1997      爱情片   9.4     否\n",
       "3    4  武状元苏乞儿     1992      喜剧片   8.2     是\n",
       "4    5    盗梦空间     2010      科幻片   9.4     否\n",
       "5    6      超体     2014      科幻片   7.4     否\n",
       "6    7     阿凡达     2009      科幻片   8.8     是\n",
       "7    8   疯狂动物城     2016      动画片   9.2     否\n",
       "8    9    魂断蓝桥     1940      爱情片   8.8     否\n",
       "9   10    千与千寻     2001      动画片   9.4     否\n",
       "10  11   西虹市首富     2018      喜剧片   6.6     否\n",
       "11  12    黑客帝国     1999      科幻片   9.1     是\n",
       "12  13   美女与野兽     1991      爱情片   8.5     否\n",
       "13  14    神偷奶爸     2010      动画片   8.6     否\n",
       "14  15    逃学威龙     1991      喜剧片   8.1     否"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movie = pd.read_excel(\"人工构造-movie表.xlsx\")\n",
    "df_movie"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "5930e5eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_before = \"\"\"\n",
    "insert into movie\n",
    "(title, pubyear, category) \n",
    "values \n",
    "\"\"\"\n",
    "\n",
    "sqls = []\n",
    "for idx, row in df_movie.iterrows():\n",
    "    title, pubyear, category = row[\"title\"], row[\"pubyear\"], row[\"category\"]\n",
    "    sqls.append(f\"\"\"('{title}', {pubyear}, '{category}')\"\"\")\n",
    "\n",
    "sql = sql_before + \", \\n\".join(sqls) + \";\"\n",
    "\n",
    "fout.write(sql + \"\\n\\n\")\n",
    "fout.flush()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3d972908",
   "metadata": {},
   "source": [
    "### 评分表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7e118665",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>movie_id</th>\n",
       "      <th>rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>9.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>8.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id  movie_id  rating\n",
       "0        1         5     9.8\n",
       "1        1        14     8.4\n",
       "2        1        10     9.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ratings = pd.read_excel(\"自动生成-评分数据.xlsx\")\n",
    "df_ratings.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "8d0ffdae",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_before = \"\"\"\n",
    "insert into ratings\n",
    "(user_id, movie_id, rating) \n",
    "values \n",
    "\"\"\"\n",
    "\n",
    "sqls = []\n",
    "for idx, row in df_ratings.iterrows():\n",
    "    user_id, movie_id, rating = int(row[\"user_id\"]), int(row[\"movie_id\"]), row[\"rating\"]\n",
    "    sqls.append(f\"\"\"({user_id}, {movie_id}, {rating})\"\"\")\n",
    "\n",
    "sql = sql_before + \", \\n\".join(sqls) + \";\"\n",
    "\n",
    "fout.write(sql + \"\\n\\n\")\n",
    "fout.flush()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "d79c96a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "fout.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5cb4d10e",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
